from PyQt5.QtWidgets import (QWidget,QGridLayout,QHBoxLayout,QVBoxLayout,QApplication,
                            QLabel,QLineEdit,QPushButton,QFileDialog,
                            QCheckBox,QTextEdit,QComboBox,QMessageBox
                            )

from mysql import MySql
from mysql import config

class SheetFieldToStr(QWidget):
    def __init__(self):
        super().__init__()
        self.mainBox = QVBoxLayout()
        self.setLayout(self.mainBox)

        hBox = QHBoxLayout()
        hBox.addWidget(QLabel("数据库地址:"))
        self.txtSqlAdd = QLineEdit()
        self.txtSqlAdd.setText("192.168.19.185:3306")
        hBox.addWidget(self.txtSqlAdd)
        hBox.addWidget(QLabel("库名:"))
        self.txtDataBase = QLineEdit("game_config")
        hBox.addWidget(self.txtDataBase)
        
        hBox.addWidget(QLabel("用户名："))
        self.txtUser = QLineEdit("root")
        hBox.addWidget(self.txtUser)

        hBox.addWidget(QLabel("密码："))
        self.txtPsw = QLineEdit("mg123456")
        hBox.addWidget(self.txtPsw)
        self.mainBox.addLayout(hBox)

        hBox = QHBoxLayout()
        hBox.addWidget(QLabel("表名:"))
        self.txtTable = QLineEdit()
        hBox.addWidget(self.txtTable)
        self.txtTable.setPlaceholderText("多个表逗号隔开")

        hBox.addWidget(QLabel("排除的字段(用'|'对应多个表):"))
        self.txtExclud = QLineEdit()
        hBox.addWidget(self.txtExclud)
        self.txtExclud.setPlaceholderText("例如:exp_rate,reward_str|active_code,end_day")
        self.mainBox.addLayout(hBox)

        hBox = QHBoxLayout()
        hBox.addStretch(1)
        self.btnOut = QPushButton("输出字段")
        hBox.addWidget(self.btnOut)
        hBox.addStretch(1)
        self.btnOut.clicked.connect(self.onClickBtn)
        self.mainBox.addLayout(hBox)

        hBox = QHBoxLayout()
        self.txtLog = QTextEdit()
        hBox.addWidget(self.txtLog)
        self.mainBox.addLayout(hBox)

        hBox = QHBoxLayout()
        hBox.addStretch(1)
        self.btnClipboard = QPushButton("复制输出")
        self.btnClipboard.setToolTip("复制输出内容到客户端配置表")
        hBox.addWidget(self.btnClipboard)
        self.btnClear = QPushButton("清空输出")
        hBox.addWidget(self.btnClear)
        self.mainBox.addLayout(hBox)
        self.btnClipboard.clicked.connect(self.onCopyOutPut)
        self.btnClear.clicked.connect(self.onClearOutPut)
        hBox.addStretch(1)
        self.mainBox.addStretch(1)

        self.mysqlEn = None
        self.tables = None
        self.excludFileds = None

    def onCopyOutPut(self):
        clipboard = QApplication.clipboard()
        outPut = self.txtLog.toPlainText()
        print("outPut",outPut)
        clipboard.setText(outPut)
        
    def onClearOutPut(self):
        self.txtLog.clear()

    def onClickBtn(self):        
        self.txtLog.clear()
        try:
            if not self.mysqlEn:
                addCfg = self.txtSqlAdd.text().split(":")
                config['host'] = addCfg[0] 
                config['port'] = int(addCfg[1]) 
                config['database'] = self.txtDataBase.text()
                config['passwd'] = self.txtPsw.text()
                config['user'] = self.txtUser.text()
                self.mysqlEn = MySql(**config)
                self.mysqlEn.isLog = False
        except Exception as e:
            print(e)
            QMessageBox.warning(self,"提示","数据库配置错误")
            return
        
        if self.txtTable.text():
            self.tables = [ x for x in self.txtTable.text().split(",") if x ]
            if self.txtExclud.text():
                exFields = [ x for x in self.txtExclud.text().split("|") if x ]
                self.excludFileds = []
                for i in range(0,len(exFields)):
                    fStr = exFields[i]
                    fArr = [x for x in fStr.split(",") if x]
                    self.excludFileds.append([])
                    for x in fArr:
                        key = self.toClientFields(x)
                        self.excludFileds[i].append(key)


            for i in range(0,len(self.tables)):
                table = self.tables[i]
                fields = self.select_struct(table,self.txtDataBase.text())
                self.printClientStr(table,fields,i)
        else:
            QMessageBox.information(self,"提示","请输入要导出的表")

        self.excludFileds = None
        self.tables=None

    
    def isValidField(self,field,tabIdx):
        print()
        if self.excludFileds and tabIdx<len(self.excludFileds):
            exFields = self.excludFileds[tabIdx]
            return  field not in exFields
        return True

    def select_struct(self,tabName,schemaName):
        sql = "SHOW TABLES LIKE '{tabName}';".format(tabName=tabName) #查找库中是否存某个表
        data = self.mysqlEn.execute(sql) #执行sql
        if len(data)<=0:
            msg = "数据库{0}中找不到表{1}".format(schemaName,tabName)
            QMessageBox.warning(self,"提示",msg)
            return None

        selectSql = "SELECT  TABLE_SCHEMA userName, \
            table_name tabName, \
            ORDINAL_POSITION colNo, \
            COLUMN_NAME colName, \
            COLUMN_TYPE dataType, \
            IS_NULLABLE isNull, \
            COLUMN_COMMENT colComment \
        FROM information_schema. COLUMNS \
        WHERE TABLE_SCHEMA = '{schemaName}' \
        AND table_name = '{tabName}' \
        ".format(schemaName=schemaName, tabName=tabName) #查询表结构的sql
        data = self.mysqlEn.execute(selectSql) #执行sql
        #print("data",type(data))
        fields = []
        for sub in data:        
            key = self.toClientFields(sub[3])
            fields.append(key)
        
        return fields 

    def toClientFields(self,field):
        keyArr = str(field).split("_")
        key = ""
        for k in keyArr:
            if k:
                k=k.title() if key else k                    
                key = key + k        
        return key
    def printClientStr(self,tabName,fields,tabIdx):
        strField = '"'+tabName+'":['
        for v in fields:
            if self.isValidField(v,tabIdx):
                strField+='"'+v+'",'
        strField = strField[:len(strField)-1]+"],"        
        self.txtLog.append(strField)
        return strField